package com.qf.web.dao.impl;

import com.qf.web.bo.BarBean;
import com.qf.web.dao.StudentDao;
import com.qf.web.pojo.Student;
import com.qf.web.utils.DbUtils1;
import com.qf.web.utils.StringUtil;
import com.qf.web.vo.BarVo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class StudentDaoImpl implements StudentDao {

    QueryRunner queryRunner = new QueryRunner(DbUtils1.getDs());
    @Override
    public List<Student> getOageData(int page, int pageSize, Student student) {

        StringBuffer sb = new StringBuffer("select * from t_student ");
        if (StringUtil.isNotEmpty(student.getName())) {
            sb.append(" and name like '%"+ student.getName() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getSex()) ) {
            sb.append(" and sex like '%"+ student.getSex() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getAddress()) ) {
            sb.append(" and address like '%"+ student.getAddress() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getPhone())) {
            sb.append(" and phone like '%"+ student.getPhone() +"%'");
        }

        sb.append(" limit ?, ?");

        String sql = sb.toString().replaceFirst("and", "where");
        System.out.println(sql);
        int start = (page - 1) * pageSize;
        List<Student> students = null;
        System.out.println(sql);
        try {
            students = queryRunner.query(sql, new BeanListHandler<>(Student.class), start, pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return students;
    }

    @Override
    public long getStudentsCount(Student student) {
        StringBuffer sb = new StringBuffer("select count(*) from t_student ");
        if (StringUtil.isNotEmpty(student.getName())) {
            sb.append(" and name like '%"+ student.getName() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getSex()) ) {
            sb.append(" and sex like '%"+ student.getSex() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getAddress()) ) {
            sb.append(" and address like '%"+ student.getAddress() +"%'");
        }
        if (StringUtil.isNotEmpty(student.getPhone())) {
            sb.append(" and phone like '%"+ student.getPhone() +"%'");
        }
        String sql = sb.toString().replaceFirst("and","where");

        System.out.println(sql);
        try {
            long count = queryRunner.query(sql, new ScalarHandler<>());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deletStudentById(int id) {
        String sql = "delete from t_student where id = ?";
        int update = 0;
        try {
            update = queryRunner.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update t_student set name = ?, sex = ?, birthday = ?, phone = ?,address = ?, hobby = ? where id = ? ";
int update = 0;
        try {
             update = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby(), student.getId());

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public int AddStudent(Student student) {
        String sql = "insert into t_student(id, name, sex, birthday, phone, address, hobby) values (null,? ,?, ?, ?, ?, ?)";

        int Add = 0;
        try {
            Add = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getPhone(), student.getAddress(), student.getHobby());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return Add;
    }

    @Override
    public BarVo selectSexList() {
        String sql = "SELECT count(*) as count, sex FROM t_student GROUP BY sex ";

        BarVo barVo = new BarVo();
        List<String> sexList  = new ArrayList<>();
        List<Integer> sexCount  = new ArrayList<>();



        try {
            List<BarBean> barBean = queryRunner.query(sql, new BeanListHandler<>(BarBean.class));

            sexList = barBean.stream().map(BarBean::getSex).collect(Collectors.toList());

            sexCount = barBean.stream().map(BarBean::getCount).collect(Collectors.toList());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        barVo.setSexList(sexList);
        barVo.setSexCount(sexCount);
        return barVo;
    }


}
